package work5;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

public class Test {
    public static void main(String[] args) {
        queryByName();
        queryByGender();
    }

    private static void queryByName(){
        System.out.println("查询姓名为tom的员工信息：");
        Connection connection = JDBCUtilsByProperties.getConnection();
        PreparedStatement statement = null;
        ResultSet set = null;
        try {
            statement = connection.prepareStatement("select * from employee where name = ?");
            statement.setString(1,"tom");
            set = statement.executeQuery();
            while (set.next()){
                System.out.println("姓名:" + set.getString("name") + "  性别:" +set.getString("gender") +"  工资："
                        + set.getDouble("salary") + "  奖金:" + set.getString("bonus") + "  入职时间" + set.getDate("join_date") );
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JDBCUtilsByProperties.close(connection,statement,set);
        }
    }

    private static void queryByGender(){
        System.out.println("查询性别为女的所有员工信息：");
        QueryRunner runner = new QueryRunner();
        Connection connection = JDBCUtilsByProperties.getConnection();
        String sql = "select * from employee where gender = '女'";
        try {
            List<EmployeeBean> list = runner.query(connection,sql,new BeanListHandler<>(EmployeeBean.class));
            for (int i = 0; i < list.size(); i++) {
                System.out.println(list.get(i).toString());
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JDBCUtilsByProperties.close(connection,null);
        }
    }
}
